/**

 * @Description:TODO

 * @author:liyang

 * @time:2017年8月5日 下午2:36:40

 */
package com.thor.palmmall.dao.rpt.sales;

import javax.persistence.EntityManager;
import javax.persistence.PersistenceContext;
import javax.persistence.Query;

import com.thor.commons.util.StringUtil;
import com.thor.palmmall.api.rpt.sales.DailySalesReportParam;

/**
 * @author:liyang
 * @time:2017年8月5日 下午2:36:40
 * @since:1.0
 */
public class SalesReportSqlBuilder {

  public static SalesReportSqlBuilder getInstance() {
    if (instance == null)
      instance = new SalesReportSqlBuilder();
    return instance;
  }

  public Query buildMonthSalesReportSql(DailySalesReportParam param) {

    StringBuffer sb = new StringBuffer();
    sb.append(
        "select DATE_FORMAT(pc.date,'%Y-%m') ,sum(pt.amount),pt.storeId from pcalendar pc left join ");

    sb.append("(");

    // from
    sb.append(
        " select pm.fdate as fdate,sum(pm.amount) as amount, pm.storeId as storeId from PMDailySales pm");

    // where
    sb.append(" where 1=1 ");
    if (StringUtil.isNullOrBlank(param.getStoreId()) == false)
      sb.append(" and pm.storeId = :storeId");
    if (StringUtil.isNullOrBlank(param.getCooperationType()) == false)
      sb.append(" and pm.cooperationType = :cooperationType");
    if (param.getDate() != null)
      sb.append(" and pm.fdate = :date");
    if (param.getBeginDate() != null)
      sb.append(" and pm.fdate >= :beginDate");
    if (param.getEndDate() != null)
      sb.append(" and pm.fdate <= :endDate");
    if (param.getIsMainShop() != null)
      sb.append(" and pm.isMainShop = :isMainShop");
    if (param.getMonth() != null)
      sb.append(" and year(pm.fdate) = :year and month(pm.fdate)= :month");
    if (param.getStoreIds() != null && param.getStoreIds().size() > 0) {
      sb.append(" and pm.storeId in (:storeIds)");
    }

    sb.append(" group by DATE_FORMAT(pm.fdate,'%Y-%m')");

    sb.append(" ) as pt  on pt.fdate = pc.date");

    sb.append(" where pc.date>=:beginDate and pc.date <=:endDate ");

    sb.append(" group by DATE_FORMAT(pc.date,'%Y-%m')");

    Query query = em.createNativeQuery(sb.toString());
    if (StringUtil.isNullOrBlank(param.getStoreId()) == false)
      query.setParameter("storeId", param.getStoreId());
    if (StringUtil.isNullOrBlank(param.getCooperationType()) == false)
      query.setParameter("cooperationType", param.getCooperationType());
    if (param.getDate() != null)
      query.setParameter("date", param.getDate());
    if (param.getBeginDate() != null)
      query.setParameter("beginDate", param.getBeginDate());
    if (param.getEndDate() != null)
      query.setParameter("endDate", param.getEndDate());
    if (param.getIsMainShop() != null)
      query.setParameter("isMainShop", param.getIsMainShop());
    if (param.getMonth() != null) {
      query.setParameter("year", param.getMonth().getYear());
      query.setParameter("month", param.getMonth().getMonth());
    }
    if (param.getStoreIds() != null && param.getStoreIds().size() > 0) {
      query.setParameter("storeIds", param.getStoreIds());
    }
    return query;

  }

  public Query buildDailySalesReportSql(DailySalesReportParam param) {

    StringBuffer sb = new StringBuffer();
    sb.append(
        "select DATE_FORMAT(pc.date,'%Y-%m-%d') ,pt.amount,pt.storeId from pcalendar pc left join ");

    sb.append("(");

    // from
    sb.append(
        " select pm.fdate as fdate,sum(pm.amount) as amount, pm.storeId as storeId from PMDailySales pm");

    // where
    sb.append(" where 1=1 ");
    if (StringUtil.isNullOrBlank(param.getStoreId()) == false)
      sb.append(" and pm.storeId = :storeId");
    if (StringUtil.isNullOrBlank(param.getCooperationType()) == false)
      sb.append(" and pm.cooperationType = :cooperationType");
    if (param.getDate() != null)
      sb.append(" and pm.fdate = :date");
    if (param.getBeginDate() != null)
      sb.append(" and pm.fdate >= :beginDate");
    if (param.getEndDate() != null)
      sb.append(" and pm.fdate <= :endDate");
    if (param.getIsMainShop() != null)
      sb.append(" and pm.isMainShop = :isMainShop");
    if (param.getMonth() != null)
      sb.append(" and year(pm.fdate) = :year and month(pm.fdate)= :month");
    if (param.getStoreIds() != null && param.getStoreIds().size() > 0) {
      sb.append(" and pm.storeId in (:storeIds)");
    }

    sb.append(" group by pm.fdate");

    sb.append(" ) as pt  on pt.fdate = pc.date");

    sb.append(" where pc.date>=:beginDate and pc.date <=:endDate ");

    Query query = em.createNativeQuery(sb.toString());
    if (StringUtil.isNullOrBlank(param.getStoreId()) == false)
      query.setParameter("storeId", param.getStoreId());
    if (StringUtil.isNullOrBlank(param.getCooperationType()) == false)
      query.setParameter("cooperationType", param.getCooperationType());
    if (param.getDate() != null)
      query.setParameter("date", param.getDate());
    if (param.getBeginDate() != null)
      query.setParameter("beginDate", param.getBeginDate());
    if (param.getEndDate() != null)
      query.setParameter("endDate", param.getEndDate());
    if (param.getIsMainShop() != null)
      query.setParameter("isMainShop", param.getIsMainShop());
    if (param.getMonth() != null) {
      query.setParameter("year", param.getMonth().getYear());
      query.setParameter("month", param.getMonth().getMonth());
    }
    if (param.getStoreIds() != null && param.getStoreIds().size() > 0) {
      query.setParameter("storeIds", param.getStoreIds());
    }
    return query;

  }

  public Query buildMonthFloorReportSql(DailySalesReportParam param) {
    StringBuffer sb = new StringBuffer();
    // from
    sb.append(
        " select DATE_FORMAT(pm.fdate,'%Y-%m-%d'),sum(pm.amount),pm.storeId,pm.floor from PMDailySales pm");
    // where
    sb.append(" where 1=1 ");
    if (StringUtil.isNullOrBlank(param.getStoreId()) == false)
      sb.append(" and pm.storeId = :storeId");
    if (param.getBeginDate() != null)
      sb.append(" and pm.fdate >= :beginDate");
    if (param.getEndDate() != null)
      sb.append(" and pm.fdate <= :endDate");
    sb.append(" group by DATE_FORMAT(pm.fdate,'%Y-%m'),floor");
    Query query = em.createNativeQuery(sb.toString());
    if (StringUtil.isNullOrBlank(param.getStoreId()) == false)
      query.setParameter("storeId", param.getStoreId());
    if (param.getBeginDate() != null)
      query.setParameter("beginDate", param.getBeginDate());
    if (param.getEndDate() != null)
      query.setParameter("endDate", param.getEndDate());

    return query;
  }

  private static SalesReportSqlBuilder instance;

  private SalesReportSqlBuilder() {
  }

  @PersistenceContext(unitName = "${thor-webui-core.persistent-unit}")
  protected EntityManager em;

}
